package com.jiepos.mpos.biz.transaction.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONObject;
import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.dao.LogStlInsSumDao;
import com.jiepos.mpos.common.entity.App_Log_Stl_Ins_SumEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.DateUtil;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * 机构分润报表业务类
 * 
 * @ProjectName: [mpos1]
 * @Package com.jiepos.mpos.biz.transaction.service
 * @author 肖志伟
 * @version 1.0
 * @since 2017年10月10日 下午2:06:29
 * @history（历次修订内容、修订人、修订时间等） @UpdateRemark [说明本次修改内容]
 * @Copyright (c) 2017 江苏杰博实信息技术有限公司
 */
@Service
public class LogStlInsSumService extends BaseService {

	@Autowired
	private LogStlInsSumDao logStlInsSumDao;

	/**
	 * 列表页查询数据
	 * 
	 * @param request
	 * @param pagination
	 * @param logStlInsSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @return
	 */
	@SuppressWarnings({"rawtypes"})
	public Map getLogStlInsSumListJson(HttpServletRequest request, Pagination pagination,
			App_Log_Stl_Ins_SumEntity logStlInsSumEntity, String stl_dt_start, String stl_dt_end) {
		JSONObject common = getSqlAndPrams(logStlInsSumEntity, stl_dt_start, stl_dt_end);
		pagination.setQueryString(common.getString("sql"));
		pagination = this.getDirectInsAuthSql(request, pagination, "t2", null);
		pagination.queryString = pagination.queryString + " order by t1.stl_dt,t1.amt desc";
		log.debug("getTransactionLogList queryString: " + pagination.queryString);
		return logStlInsSumDao.getPaginationMap(pagination, "t1", common.getObject("params", List.class));

	}

	/**
	 * 获取汇总数据
	 * 
	 * @param request
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @param custom_name
	 * @param custom_phone
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public Map getLogStlMchntInfoTotal(HttpServletRequest request, App_Log_Stl_Ins_SumEntity logStlInsSumEntity,
			String stl_dt_start, String stl_dt_end) {
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("select ");
		sqlSb.append("to_char(sum(t1.amt),'fm9999999990.00') as all_amt, ");
		sqlSb.append("to_char(sum(t1.midfee),'fm9999999990.00') as all_midfee, ");
		sqlSb.append("to_char(sum(t1.custom_stl_fee),'fm9999999990.00') as all_custom_stl_fee, ");
		sqlSb.append("to_char(sum(t1.ins_stl_fee),'fm9999999990.00') as all_ins_stl_fee, ");
		sqlSb.append("to_char(sum((t1.midfee - t1.ins_stl_fee)-(t1.midfee - t1.custom_stl_fee)),'fm9999999990.00') as all_gross_profit ");
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_LOG_STL_INS_SUM + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_cd = t2.ins_cd ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_no = t4.service_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.param_no = t5.param_no ");
		sqlSb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(logStlInsSumEntity.getIns_cd())) {
			sqlSb.append(" and t2.ins_cd = ? ");
			list.add(logStlInsSumEntity.getIns_cd());
		}
		String sql = sqlSb.toString();
		sql = this.getDirectInsAuthSql(request, sql, "t2", null);
		log.debug("getTransactionLogList queryString: " + sql);
		return logStlInsSumDao.queryForMap(sql, list);

	}

	/**
	 * 导出数据
	 * 
	 * @param fileName
	 * @param request
	 * @param response
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @param custom_name
	 * @param custom_phone
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public String executeSaveLogStlMchntSum(String fileName, HttpServletRequest request, HttpServletResponse response,
			App_Log_Stl_Ins_SumEntity logStlInsSumEntity, String stl_dt_start, String stl_dt_end) {
		JSONObject common = getSqlAndPrams(logStlInsSumEntity, stl_dt_start, stl_dt_end);
		String sql = this.getInsAuthSql(request, common.getString("sql"), "t2", null);
		sql = sql + " order by t1.stl_dt,t1.amt desc";
		List<Map> resultList = logStlInsSumDao.queryForList(sql, common.getObject("params", List.class));
		List<String> headList = new ArrayList<String>();
		headList.add("清算日期");
		headList.add("机构号");
		headList.add("机构名称");
		headList.add("业务名称");
		headList.add("业务参数名称");
		headList.add("交易金额");
		headList.add("交易笔数");
		headList.add("会员手续费");
		headList.add("会员结算手续费");
		headList.add("机构结算手续费");
		headList.add("毛利");
		return ExcelUtil.writeExcel(fileName, "机构分润报表", headList, resultList);
	}

	/**
	 * 会员清算交易汇总导出
	 * 
	 * @param request
	 * @param response
	 * @param pagination
	 * @param logStlMchntSumEntity
	 * @param stl_dt_start
	 * @param stl_dt_end
	 * @param custom_no
	 * @param custom_name
	 * @param custom_phone
	 * @return
	 */
	@SuppressWarnings({ "rawtypes", "unchecked" })
	public JSONObject getSqlAndPrams(App_Log_Stl_Ins_SumEntity logStlInsSumEntity, String stl_dt_start, String stl_dt_end) {
		StringBuffer sqlSb = new StringBuffer();
		sqlSb.append("select ");
		sqlSb.append("t1.stl_dt as stl_dt1, ");
		/*sqlSb.append("t1.stl_dt, ");*/
		sqlSb.append("TO_CHAR(TO_DATE(t1.stl_dt, 'YYYY-MM-DD'), 'yyyy-mm-dd') as stl_dt, ");
		sqlSb.append("t2.ins_cd, ");
		sqlSb.append("t2.ins_cn_nm, ");
		sqlSb.append("t4.service_no||'-'||t4.service_name as service_name, ");
		sqlSb.append("t5.param_no||'-'||t5.param_name as service_param_name, ");
		sqlSb.append("to_char(t1.amt,'fm9999999990.00') as amt, ");
		sqlSb.append("t1.cnt as cnt, ");
		sqlSb.append("to_char(t1.midfee,'fm9999999990.00') as midfee, ");
		sqlSb.append("to_char(t1.custom_stl_fee,'fm9999999990.00') as custom_stl_fee, ");
		sqlSb.append("to_char(t1.ins_stl_fee,'fm9999999990.00') as ins_stl_fee, ");
		sqlSb.append("to_char((t1.midfee - t1.ins_stl_fee)-(t1.midfee - t1.custom_stl_fee),'fm9999999990.00') as gross_profit "); // 毛利
		sqlSb.append("from ");
		sqlSb.append(Table.T_APP_LOG_STL_INS_SUM + " t1 ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_INS_INF + " t2 ");
		sqlSb.append("on t1.ins_cd = t2.ins_cd ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_INF + " t4 ");
		sqlSb.append("on t1.service_no = t4.service_no ");
		sqlSb.append("left join ");
		sqlSb.append(Table.T_APP_SERVICE_PARAM + " t5 ");
		sqlSb.append("on t1.param_no = t5.param_no ");
		sqlSb.append(" where 1=1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(stl_dt_start)) {
			Date date = DateUtil.strToDate(stl_dt_start, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt >= ? ");
			list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(stl_dt_end)) {
			Date date = DateUtil.strToDate(stl_dt_end, DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(date, "yyyyMMdd"));
		}else{
			Date yesterday = DateUtil.strToDate(DateUtil.getDecreaseDaysDate(new Date(), 2), DateUtil.FORMAT_DATE_1);
			sqlSb.append(" and t1.stl_dt <= ? ");
			list.add(DateUtil.dateToStr(yesterday, "yyyyMMdd"));
		}
		if (!StringUtils.isBlank(logStlInsSumEntity.getIns_cd())) {
			sqlSb.append(" and t2.ins_cd = ? ");
			list.add(logStlInsSumEntity.getIns_cd());
		}
		JSONObject common = new JSONObject();
		common.put("sql", sqlSb.toString());
		common.put("params", list);
		return common;
	}
}